﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace DAL
{
    public partial class Form1 : Form
    {
        public static readonly string DB_FILE = "Demo.db3";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            rbMsg.Clear();
            //查询从50条起的20条记录 
            string sql = "select * from test3 order by id desc";// limit 50 offset 20";
            SQLiteDBHelper db = new DAL.SQLiteDBHelper(DB_FILE);
            using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
            {
                while (reader.Read())
                {
                    rbMsg.AppendText(string.Format("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1))+"\r\n");
                    rbMsg.ScrollToCaret();
                }
                reader.Close();
            }
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            string dbPath = DB_FILE;
            //如果不存在改数据库文件，则创建该数据库文件 
            if (!System.IO.File.Exists(dbPath))
            {
                SQLiteDBHelper.CreateDB(dbPath);
            }
            SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
            string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
            db.ExecuteNonQuery(sql, null); 
        }

        private void button2_Click(object sender, EventArgs e)
        {
            rbMsg.Clear();
            string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
            SQLiteDBHelper db = new SQLiteDBHelper(DB_FILE);
            //db.ExecuteNonQuery("DELETE FROM Test3", null);
            for (char c = 'A'; c <= 'Z'; c++)
            {
                for (int i = 0; i < 200; i++)
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[]{ 
                                                 new SQLiteParameter("@Name",c+i.ToString()), 
                                         new SQLiteParameter("@TypeName",c.ToString()), 
                                         new SQLiteParameter("@addDate",DateTime.Now), 
                                         new SQLiteParameter("@UpdateTime",DateTime.Now.Date), 
                                         new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()), 
                                         new SQLiteParameter("@Comments","测试用的数据，增长数据库容量，呵呵"+i) 
                                         };
                    db.ExecuteNonQuery(sql, parameters);
                    rbMsg.AppendText(c.ToString()+"--"+i.ToString()+"\r\n");
                    rbMsg.ScrollToCaret();
                }
            } 
        }

        private void button3_Click(object sender, EventArgs e)
        {
            rbMsg.Clear();
            SQLiteConnection cn = new SQLiteConnection("Data Source=" + DB_FILE);
            string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
            cn.Open();
            SQLiteCommand cmd = new SQLiteCommand(cn);
            cmd.CommandText = "select count(*) from Test3";
            rbMsg.AppendText(string.Format("记录数：{0}\r\n", (int)(Int64)cmd.ExecuteScalar()));
            rbMsg.ScrollToCaret();

            //cmd.CommandText = "DELETE FROM Test3";
            //cmd.ExecuteNonQuery();
            cmd.CommandText = "select count(*) from Test3";
            rbMsg.AppendText(string.Format("记录数：{0}\r\n", (int)(Int64)cmd.ExecuteScalar()));
            rbMsg.ScrollToCaret();

            SQLiteTransaction tran = cn.BeginTransaction();
            cmd.Transaction = tran;
            try
            {
                for (char c = 'A'; c <= 'Z'; c++)
                {
                    for (int i = 0; i < 50000; i++)
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddWithValue("@Name", c + i.ToString());
                        cmd.Parameters.AddWithValue("@TypeName", c.ToString());
                        cmd.Parameters.AddWithValue("@addDate", DateTime.Now);
                        cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now.Date);
                        cmd.Parameters.AddWithValue("@Time", DateTime.Now.ToShortTimeString());
                        cmd.Parameters.AddWithValue("@Comments", "测试数据-" + i);
                        cmd.ExecuteNonQuery();
                    }
                    rbMsg.AppendText(c.ToString() + "=>");
                    rbMsg.ScrollToCaret();
                }
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                rbMsg.AppendText("执行出错！" + "\r\n");
                rbMsg.ScrollToCaret();
            }
            finally
            {
                cmd.CommandText = "select count(*) from Test3\r\n";
                rbMsg.AppendText(string.Format("记录数：{0}",(int)(Int64)cmd.ExecuteScalar()));
                rbMsg.ScrollToCaret();
                cn.Close();
            }


        }
    }
}
